package br.com.simora.execucao;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.inject.Named;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import br.com.simora.musica.Artista;
import br.com.simora.musica.Musica;
import br.com.simora.persistence.AbstractDAO;
import br.com.simora.radio.Radio;

@Named("execucaoDAO")
public class ExecucaoDAO extends AbstractDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -8682591004203973083L;

	public List<Execucao> recuperaExecucaoPorRadio(Radio radio, Date dataInicio, Date dataFim) {
		CriteriaBuilder qb = manager.getCriteriaBuilder();
		CriteriaQuery<Execucao> cq = qb.createQuery(Execucao.class);
		Root<Execucao> tb = cq.from(Execucao.class);
		Predicate eq3 = null;
		Predicate eq4 = null;
		Expression<Date> dtPrevIni = tb.get("dataHora");
		Expression<Date> dtPrevFim = tb.get("dataHora");
		eq3 = qb.greaterThanOrEqualTo(dtPrevIni, dataInicio);
		eq4 = qb.lessThanOrEqualTo(dtPrevFim, dataFim);
		cq.select(tb);
		cq.where(qb.equal(tb.get("radio"), radio), qb.and(eq3, eq4));
		return (List<Execucao>) recuperaLista(montaQuery(cq));
	}
	
	public List<Musica> recuperaMusicasMaisTocadasPeriodo(Date dataInicio, Date dataFim) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String dataInicioStr = df.format(dataInicio);
        String dataFimStr = df.format(dataFim);
		String query = "select "
							+ "count(e.musica_id) as total, m.id as id " +
						"from " +
							"execucao e, " +
							"musica m " +
						"where " +
							"m.nome != '' and " +
							"m.nome != 'madrugada mundo livre' and " +
							"m.nome not like ('programa%') and " +
							"e.musica_id = m.id and " +
							"e.data_hora between  '" + dataInicioStr + "' AND '" + dataFimStr + "' " +
						"group by m.id " +
						"order by total desc " +
						"limit 10";
		Query q = manager.createNativeQuery(query);
		List<?> list = recuperaLista(q);
		List<Musica> t = new ArrayList<Musica>(); 
		for(Object m : list){
			Object[] o = (Object[]) m;
			Musica musica = manager.find(Musica.class, o[1]);
			musica.setTotal((Long) o[0]);
			t.add(musica);
		}
		return t;
	}
	
	public List<Musica> recuperaMusicasMaisTocadasFiltro(Musica musica, Date dataInicio, Date dataFim, 
			Radio radio, Integer quantidadeRegistros, Integer pagina) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String dataInicioStr = df.format(dataInicio);
        String dataFimStr = df.format(dataFim);
		String query = "select "
							+ "count(e.musica_id) as total, m.id as id " +
						"from " +
							"execucao e, " +
							"radio r, " +
							"musica m " +
						"where ";
		if(null != radio.getRegiao() && !"".equals(radio.getRegiao())){
			query += "r.regiao = '" + radio.getRegiao() + "' and ";
			if(null != radio.getEstado() && !"".equals(radio.getEstado())){
				query += " r.estado = '" + radio.getEstado() + "' and ";
				if(null != radio.getCidade() && !"".equals(radio.getCidade())){
					query += " r.cidade = '" + radio.getCidade() + "' and ";
					if(null != radio.getId()){
						query += " r.id = " + radio.getId() + " and ";
					}
				}
			}
		}
		if(null != musica && musica.getId() != null){
			query += "m.id = " + musica.getId() + " and ";
		}
		if(null != musica && musica.getArtista() != null && musica.getArtista().getId() != null){
			query += "m.artista_id = " + musica.getArtista().getId() + " and ";
		}
					query += "m.nome != '' and " +
							"r.id = e.radio_id and " +
							"m.nome != 'madrugada mundo livre' and " +
							"m.nome not like ('programa%') and " +
							"e.musica_id = m.id and " +
							"e.data_hora between  '" + dataInicioStr + " 00:00:01' AND '" + dataFimStr + " 23:59:59' " +
						"group by m.id " +
						"order by total desc ";
					if(quantidadeRegistros != null){
						Integer ultimoRegistro = (pagina-1) * quantidadeRegistros;
						query += "limit " + quantidadeRegistros + " OFFSET " + ultimoRegistro;
					}
		Query q = manager.createNativeQuery(query);
		List<?> list = recuperaLista(q);
		List<Musica> t = new ArrayList<Musica>(); 
		for(Object m : list){
			Object[] o = (Object[]) m;
			Musica musicaT = manager.find(Musica.class, o[1]);
			musicaT.setTotal((Long) o[0]);
			t.add(musicaT);
		}
		return t;
	}
	
	public List<Artista> recuperaArtistasMaisTocadosPeriodo(Date dataInicio, Date dataFim) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String dataInicioStr = df.format(dataInicio);
        String dataFimStr = df.format(dataFim);
		String query = "select "
							+ "count(a.id) as total, a.id as id " +
						"from " +
							"execucao e, " +
							"musica m, " +
							"artista a " +
						"where " +
						 	"a.nome != '' and " +
							"e.musica_id = m.id and " +
							"a.id = m.artista_id and " +
							"a.nome not like 'programa%' and " +
							"e.data_hora between  '" + dataInicioStr + "' AND '" + dataFimStr + "' " +
						"group by a.id " +
						"order by total desc " +
						"limit 10";
		Query q = manager.createNativeQuery(query);
		List<?> list = recuperaLista(q);
		List<Artista> t = new ArrayList<Artista>(); 
		for(Object m : list){
			Object[] o = (Object[]) m;
			Artista artista = manager.find(Artista.class, o[1]);
			artista.setTotal((Long) o[0]);
			t.add(artista);
		}
		return t;
	}

	public List<Execucao> recuperaExecucaoPorFiltro(Radio radio, Musica musica, Date dataInicio, Date dataFim, Integer quantidadeRegistros, Integer pagina) {
		List<Radio> listaRadios = null;
		if(null != radio && null != radio.getId()){
			radio = manager.find(Radio.class, radio.getId());
		} else {
			listaRadios = recuperaRadioPorFiltro(radio);
		}
		CriteriaBuilder qb = manager.getCriteriaBuilder();
		CriteriaQuery<Execucao> cq = qb.createQuery(Execucao.class);
		
		Root<Execucao> tb = cq.from(Execucao.class);
		
		Predicate eq2 = null;
		if(musica != null && musica.getId() != null){
			musica = manager.find(Musica.class, musica.getId());
			eq2 = qb.equal(tb.get("musica"), musica);
		}
		
		Predicate eq3 = null;
		Predicate eq4 = null;
		Expression<Date> dtPrevIni = tb.get("dataHora");
		Expression<Date> dtPrevFim = tb.get("dataHora");
		eq3 = qb.greaterThanOrEqualTo(dtPrevIni, dataInicio);
		eq4 = qb.lessThanOrEqualTo(dtPrevFim, dataFim);
		cq.select(tb);
		Expression<String> exp = tb.get("radio");
		Predicate predicateRadio = null; 
		if(listaRadios != null && listaRadios.size() > 0){
			List<Integer> radiosIds = new ArrayList<Integer>();
			for(Radio radioT : listaRadios){
				radiosIds.add(radioT.getId());
			}
			
			predicateRadio = exp.in(radiosIds);
			
		} else {
			predicateRadio = qb.equal(exp, radio);
		}
		if(eq2 != null){
			cq.where(predicateRadio, qb.and(eq3, eq4, eq2));
		} else {
			cq.where(predicateRadio, qb.and(eq3, eq4));
		}
		cq.orderBy(qb.desc(tb.get("dataHora")));
		List<Execucao> lista =  (List<Execucao>) recuperaLista(montaQueryCache(cq, quantidadeRegistros, pagina));
		if(musica != null && musica.getArtista() != null && null != musica.getArtista().getId()){
			List<Execucao> execFim = new ArrayList<Execucao>();
			for(Execucao e : lista){
				if(musica.getArtista().getId().intValue() == e.getMusica().getArtista().getId().intValue()){
					execFim.add(e);
				}
			}
			return execFim;
		}
		return lista;
	}
	
	public List<Radio> recuperaRadioPorFiltro(Radio radio) {
		CriteriaBuilder qb = manager.getCriteriaBuilder();
		CriteriaQuery<Radio> cq = qb.createQuery(Radio.class);
		Root<Radio> tb = cq.from(Radio.class);
		cq.select(tb);
		Predicate p1 = null;
		Predicate p2 = null;
		if(!"".equals(radio.getEstado()) && null != radio.getEstado()){
			p1 = qb.equal(tb.get("estado"), radio.getEstado());
			cq.where(p1);
		}
		if(!"".equals(radio.getCidade()) && null != radio.getCidade()){
			p2 = qb.equal(tb.get("cidade"), radio.getCidade());
			cq.where(p1, p2);
		}
		
		return (List<Radio>) recuperaLista(montaQuery(cq));
	}

	public Execucao recuperaUltimaExecucaoRadio(Radio radio) {
		String query = "SELECT id, radio_id, musica_id, data_hora from execucao "
				+ "where "
				+ "radio_id="+ radio.getId() 
				+ " and data_hora=(select max(data_hora) from execucao where radio_id="+ radio.getId()+" )";
		Query q = manager.createNativeQuery(query, Execucao.class);
		Execucao t = (Execucao) recuperaRegistro(q);
		if(t != null){
			return manager.find(Execucao.class, t.getId());
		}
		return null;
	}

}